﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.ESS.MODEL;

namespace KPIS.GERP.ESS.DAL
{
    public class IcmPrePurchaseRequestDAL
    { 
        public DataSet GetPrePurchaseRequest(int Sequence)
        {
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            string strSQL;
            DataSet ds;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", Sequence);

                strSQL = "SELECT ppr.*"
                            + ", pt.ICM_PC_TYPE_NAME"
                            + ", rs.RECORD_STATUS_NAME"
                            + ", dc.ICM_DUR_COUNT_NAME"
                            + ", dt.ICM_DUR_TYPE_NAME"
                            + ", yt.YEAR_TYPE_NAME"
                            + ", js.JOB_SITE_NAME"
                            + ", um.ICM_PPR_USE_METHOD_NAME"
                            + ", wa.SCOPE_SEQ"
                            + ", sub.ALL_BUDGET AS BGM_ALL_BUDGET"
                            + ", 0 AS BGM_REMAIN_BUDGET"
                            + ", CASE WHEN ppr.ICM_PPR_FOR_SEQ = 1 THEN 'เพื่อใช้ภายในหน่วยงาน' ELSE 'ใช้ส่วนรวม' END AS ICM_PPR_FOR_NAME"
                        + " FROM ICM_PRE_PURCHASE_REQUEST ppr"
                            + " INNER JOIN ICM_MAS_PURCHASE_TYPE pt ON ppr.ICM_PC_TYPE_SEQ = pt.ICM_PC_TYPE_SEQ"
                            + " INNER JOIN SYS_RECORD_STATUS rs ON ppr.RECORD_STATUS = rs.RECORD_STATUS"
                            + " INNER JOIN SYS_YEAR_TYPE yt ON ppr.YEAR_TYPE_SEQ = yt.YEAR_TYPE_SEQ"
                            + " LEFT JOIN ICM_SYS_DURATION_COUNT dc ON ppr.ICM_DUR_COUNT_SEQ = dc.ICM_DUR_COUNT_SEQ"
                            + " LEFT JOIN ICM_SYS_DURATION_TYPE dt ON ppr.ICM_DUR_TYPE_SEQ = dt.ICM_DUR_TYPE_SEQ"
                            + " INNER JOIN HRM_MP_JOB_SITES js ON js.JOB_SITE_SEQ = ppr.REQUEST_JOB_SITE_SEQ"
                            + " INNER JOIN HRM_MP_WORK_AREAS wa ON js.WORK_AREA_SEQ = wa.WORK_AREA_SEQ"
                            + " INNER JOIN HRM_MP_SCOPES ms ON wa.SCOPE_SEQ = ms.SCOPE_SEQ"
                            + " LEFT JOIN ICM_SYS_PPR_USE_METHOD um ON um.ICM_PPR_USE_METHOD_SEQ = ppr.ICM_PPR_USE_METHOD_SEQ"
                            + " LEFT JOIN (select distinct it.icm_ppr_seq"
                                    + ", coalesce(poe.unit_price, poe.bath_amount, 0) as all_budget"
                                + " from bgm_project_operation_expense poe"
                                + " inner join bgm_project_operation po on poe.bgm_proj_oper_seq = po.bgm_proj_oper_seq"
                                + " inner join bgm_project_expense pe on poe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq"
                                + " inner join bgm_mas_expense ex on pe.bgm_exp_seq = ex.bgm_exp_seq"
                                + " inner join icm_pre_purchase_request_item it on ex.bgm_exp_seq = it.bgm_exp_seq"
                                + " where it.icm_ppr_seq = @seq"
                                + ")sub ON ppr.ICM_PPR_SEQ = sub.ICM_PPR_SEQ"
                        + " WHERE ppr.ICM_PPR_SEQ = @seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                //dr = ds.Tables[0].Rows;
                //if (dr.Count > 0)
                //{
                    //SysPprPriorityInfo infoPprPriority = new SysPprPriorityInfo();
                    //SysPprTypeInfo infoPprType = new SysPprTypeInfo();
                    //SysPprMethodInfo infoPprMethod = new SysPprMethodInfo();
                    //SysPprForInfo infoPprFor = new SysPprForInfo();
                    //MasterPurchaseTypeInfo infoPurchaseType = new MasterPurchaseTypeInfo();
                    //SysYearTypeInfo infoYearType = new SysYearTypeInfo();
                    //HrmJobSiteInfo infoJobSite = new HrmJobSiteInfo();
                    //HrmJobSiteInfo infoJobSiteDirect = new HrmJobSiteInfo();
                    //FimMoneyRequestInfo infoMoneyRequest = new FimMoneyRequestInfo();
                    //HrmEmployeeInfo infoEmployeeRequest = new HrmEmployeeInfo();
                    //HrmJobSiteInfo infoJobSiteRequest = new HrmJobSiteInfo();
                    //HrmEmployeeInfo infoEmployeeConfirmed = new HrmEmployeeInfo();
                    //HrmEmployeeInfo infoEmployeeApproved = new HrmEmployeeInfo();
                    //SysDurationTypeInfo infoDurationType = new SysDurationTypeInfo();
                    //SysDurationCountInfo infoDurationCount = new SysDurationCountInfo();
                    //SysProgressInfo infoProgress = new SysProgressInfo();
                    //SysPprUseMethodInfo infoPprUseMethod = new SysPprUseMethodInfo();
                    //RecordInfo infoRecord = new RecordInfo();
                    //SysRecordStatusInfo infoStatus = new SysRecordStatusInfo();
                    //SysScopeInfo infoHrmScope = new SysScopeInfo();
                    //info.Seq = Convert.ToInt32(dr[0]["ICM_PPR_SEQ"]);
                    //info.Ref_no = (dr[0]["ICM_PPR_REF_NO"]).ToString();
                    //info.Name = (dr[0]["ICM_PPR_NAME"]).ToString();
                    //info.Reason = (dr[0]["ICM_PPR_REASON"]).ToString();
                    //info.Year = (Convert.IsDBNull(dr[0]["BGM_YEAR"]) ? null : (Nullable<int>)(dr[0]["BGM_YEAR"]));
                    //info.DateRequested = (Convert.IsDBNull(dr[0]["REQUEST_DATE"]) ? null : (Nullable<DateTime>)(dr[0]["REQUEST_DATE"]));
                    //info.DateConfirmed = (Convert.IsDBNull(dr[0]["CONFIRM_DATE"]) ? null : (Nullable<DateTime>)(dr[0]["CONFIRM_DATE"]));
                    //info.DateApproved = (Convert.IsDBNull(dr[0]["APPROVED_WHEN"]) ? null : (Nullable<DateTime>)(dr[0]["APPROVED_WHEN"]));
                    //info.DateRequired = (Convert.IsDBNull(dr[0]["REQUIRE_DATE"]) ? null : (Nullable<DateTime>)(dr[0]["REQUIRE_DATE"]));
                    //info.DurationRequired = (Convert.IsDBNull(dr[0]["REQUIRE_DURATION"]) ? null : (Nullable<int>)(dr[0]["REQUIRE_DURATION"]));
                    //infoPprFor.Seq = (Convert.IsDBNull(dr[0]["ICM_PPR_FOR_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_PPR_FOR_SEQ"]));
                    //infoPurchaseType.Seq = (Convert.IsDBNull(dr[0]["ICM_PC_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_PC_TYPE_SEQ"]));
                    //infoPurchaseType.Name = (dr[0]["ICM_PC_TYPE_NAME"]).ToString();
                    //infoYearType.Seq = (Convert.IsDBNull(dr[0]["YEAR_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[0]["YEAR_TYPE_SEQ"]));
                    //infoYearType.Name = (dr[0]["YEAR_TYPE_NAME"]).ToString();
                    //infoJobSite.Seq = (Convert.IsDBNull(dr[0]["JOB_SITE_SEQ"]) ? null : (Nullable<int>)(dr[0]["JOB_SITE_SEQ"]));
                    //infoJobSite.Name = dr[0]["JOB_SITE_NAME"].ToString();
                    //infoJobSiteDirect.Seq = (Convert.IsDBNull(dr[0]["JOB_SITE_DIRECT_SEQ"]) ? null : (Nullable<int>)(dr[0]["JOB_SITE_DIRECT_SEQ"]));
                    //infoHrmScope.Seq = (Convert.IsDBNull(dr[0]["SCOPE_SEQ"]) ? null : (Nullable<int>)(dr[0]["SCOPE_SEQ"]));
                    //infoJobSiteDirect.InfoScope = infoHrmScope;
                    //infoEmployeeRequest.Seq = (Convert.IsDBNull(dr[0]["REQUEST_EMP_SEQ"]) ? null : (Nullable<int>)(dr[0]["REQUEST_EMP_SEQ"]));
                    //infoJobSiteRequest.Seq = (Convert.IsDBNull(dr[0]["REQUEST_JOB_SITE_SEQ"]) ? null : (Nullable<int>)(dr[0]["REQUEST_JOB_SITE_SEQ"]));
                    //infoJobSiteRequest.Name = (Convert.IsDBNull(dr[0]["JOB_SITE_NAME"]) ? "" : (dr[0]["JOB_SITE_NAME"]).ToString());
                    //infoEmployeeConfirmed.Seq = (Convert.IsDBNull(dr[0]["CONFIRM_EMP_SEQ"]) ? null : (Nullable<int>)(dr[0]["CONFIRM_EMP_SEQ"]));
                    //infoDurationType.Seq = (Convert.IsDBNull(dr[0]["ICM_DUR_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_DUR_TYPE_SEQ"]));
                    //infoDurationType.Name = (dr[0]["ICM_DUR_TYPE_NAME"]).ToString();
                    //infoDurationCount.Seq = (Convert.IsDBNull(dr[0]["ICM_DUR_COUNT_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_DUR_COUNT_SEQ"]));
                    //infoDurationCount.Name = (dr[0]["ICM_DUR_COUNT_NAME"]).ToString();
                    //infoProgress.Seq = (Convert.IsDBNull(dr[0]["ICM_PROG_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_PROG_SEQ"]));
                    //infoStatus.Status = (dr[0]["RECORD_STATUS"]).ToString();
                    //infoStatus.Name = (dr[0]["RECORD_STATUS_NAME"]).ToString();
                    //infoRecord.Created_by = (Convert.IsDBNull(dr[0]["CREATED_BY"]) ? null : (Nullable<int>)(dr[0]["CREATED_BY"]));
                    //infoRecord.Created_when = (Convert.IsDBNull(dr[0]["CREATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[0]["CREATED_WHEN"]));
                    //infoRecord.Updated_by = (Convert.IsDBNull(dr[0]["UPDATED_BY"]) ? null : (Nullable<int>)(dr[0]["UPDATED_BY"]));
                    //infoRecord.Updated_when = (Convert.IsDBNull(dr[0]["UPDATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[0]["UPDATED_WHEN"]));
                    //infoRecord.Restored_by = (Convert.IsDBNull(dr[0]["RESTORED_BY"]) ? null : (Nullable<int>)(dr[0]["RESTORED_BY"]));
                    //infoRecord.Restored_when = (Convert.IsDBNull(dr[0]["RESTORED_WHEN"]) ? null : (Nullable<DateTime>)(dr[0]["RESTORED_WHEN"]));
                    //infoPprUseMethod.Seq = (Convert.IsDBNull(dr[0]["icm_ppr_use_method_seq"]) ? null : (Nullable<int>)(dr[0]["icm_ppr_use_method_seq"]));
                    //infoPprUseMethod.Name = dr[0]["icm_ppr_use_method_name"].ToString();
                    //info.InfoPprPriority = infoPprPriority;
                    //info.InfoPprType = infoPprType;
                    //info.InfoPprMethod = infoPprMethod;
                    //info.InfoPprFor = infoPprFor;
                    //info.InfoPurchaseType = infoPurchaseType;
                    //info.InfoYearType = infoYearType;
                    //info.InfoJobSite = infoJobSite;
                    //info.InfoJobSiteDirect = infoJobSiteDirect;
                    //info.InfoEmployeeRequest = infoEmployeeRequest;
                    //info.InfoJobSiteRequest = infoJobSiteRequest;
                    //info.InfoEmployeeConfirmed = infoEmployeeConfirmed;
                    //info.InfoDurationType = infoDurationType;
                    //info.InfoDurationCount = infoDurationCount;
                    //info.InfoProgress = infoProgress;
                    //info.InfoPprUseMethod = infoPprUseMethod;
                    //infoRecord.InfoStatus = infoStatus;
                    //info.InfoRecord = infoRecord;
                //}
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return ds;
        }
        public DataSet GetPrePurchaseRequestItems(Nullable<int> Sequence, Nullable<int> PprSequence)
        {
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            string strSQL;
            DataSet ds;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@PprSequence", PprSequence);
                dbManager.AddParameters(2, "@ActiveRecord", SystemInfo._ActiveRecordStatus);

                strSQL = "SELECT pi.*"
                            + ", po.BGM_PROJ_OPER_SEQ"
                            + ", po.BGM_PROJ_OPER_NAME"
                            + ", s.BGM_SOURCE_NAME"
                            + ", x.BGM_EXP_NAME"
                            + ", i.ICM_RES_ITEM_NAME"
                            + ", u.ICM_UNIT_SEQ"
                            + ", u.ICM_UNIT_NAME"
                            + ", (pi.ICM_PPR_ITEM_QTY * pi.ICM_PPR_ITEM_PRICE) AS AMOUNT"
                        + " FROM ICM_PRE_PURCHASE_REQUEST_ITEM pi"
                            + " INNER JOIN ICM_PRE_PURCHASE_REQUEST ppr ON pi.ICM_PPR_SEQ = ppr.ICM_PPR_SEQ"
                            + " LEFT JOIN BGM_PROJECT_OPERATION_EXPENSE poe ON pi.BGM_PROJ_OPER_EXP_SEQ = poe.BGM_PROJ_OPER_EXP_SEQ"
                            + " LEFT JOIN BGM_PROJECT_OPERATION po ON poe.BGM_PROJ_OPER_SEQ = po.BGM_PROJ_OPER_SEQ"
                            + " LEFT JOIN BGM_PROJECT_EXPENSE pe ON pi.BGM_PROJ_EXP_SEQ = pe.BGM_PROJ_EXP_SEQ"
                            + " LEFT JOIN BGM_PROJECT p ON pe.BGM_PROJ_SEQ = p.BGM_PROJ_SEQ"
                            + " LEFT JOIN BGM_MAS_SOURCE s ON pi.BGM_SOURCE_SEQ = s.BGM_SOURCE_SEQ"
                            + " LEFT JOIN BGM_MAS_EXPENSE x ON pi.BGM_EXP_SEQ = x.BGM_EXP_SEQ"
                            + " LEFT JOIN ICM_MAS_RESOURCE_ITEM i ON pi.ICM_RES_ITEM_SEQ = i.ICM_RES_ITEM_SEQ"
                            + " LEFT JOIN ICM_MAS_UNIT u ON i.ICM_UNIT_SEQ = i.ICM_UNIT_SEQ"
                        + " WHERE pi.RECORD_STATUS = @ActiveRecord"
                        + (Sequence.HasValue ? " AND pi.ICM_PPR_ITEM_SEQ = @Sequence" : "")
                        + (PprSequence.HasValue ? " AND pi.ICM_PPR_SEQ = @PprSequence" : "");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return ds;
        }
        public DataSet GetPrePurchaseRequestCommittee(Nullable<int> Sequence, Nullable<int> PprSequence)
        {
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            string strSQL;
            DataSet ds = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Sequence", Sequence);
                dbManager.AddParameters(2, "@PprSequence", PprSequence);

                strSQL = "SELECT pc.*"
                            + ", f.ICM_COM_FUNC_NAME"
                            + ", p.ICM_COM_POS_NAME"
                            + ", CASE WHEN pc.EMP_SEQ IS NOT NULL THEN CONCAT(np.PREFIX_NAME, e.FIRST_NAME, ' ', e.LAST_NAME)"
                                + " ELSE pc.ICM_PPR_COM_EX_NAME END AS COMMITTEE_NAME"
                            + ", e.FIRST_NAME"
                            + ", e.LAST_NAME"
                            + ", c.CLASS_NAME"
                            + ", pos.POS_NAME"
                            + ", js.JOB_SITE_NAME"
                            + ", ct.ICM_COM_TYPE_NAME"
                        + " FROM ICM_PRE_PURCHASE_REQUEST_COMMITTEE pc"
                            + " INNER JOIN ICM_SYS_COMMITTEE_TYPE ct ON pc.ICM_COM_TYPE_SEQ = ct.ICM_COM_TYPE_SEQ"
                            + " LEFT JOIN ICM_MAS_COMMITTEE_FUNCTION f ON pc.ICM_COM_FUNC_SEQ = f.ICM_COM_FUNC_SEQ"
                            + " LEFT JOIN ICM_MAS_COMMITTEE_POSITION p ON pc.ICM_COM_POS_SEQ = p.ICM_COM_POS_SEQ"
                            + " LEFT JOIN HRM_PS_EMPLOYEES e ON pc.EMP_SEQ = e.EMP_SEQ"
                            + " LEFT JOIN HRM_MP_CLASSES c ON e.CLASS_SEQ = c.CLASS_SEQ"
                            + " LEFT JOIN HRM_MP_POSITIONS pos ON pos.POS_SEQ = e.POS_SEQ"
                            + " LEFT JOIN HRM_MP_JOB_SITES js ON e.WORKING_JOB_SITE_SEQ = js.JOB_SITE_SEQ"
                            + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                        + " WHERE pc.RECORD_STATUS <> @delete_status"
                        + (Sequence.HasValue ? " AND pc.ICM_PPR_COM_SEQ = @Sequence" : "")
                        + (PprSequence.HasValue ? " AND pc.ICM_PPR_SEQ = @PprSequence" : "")
                        + " ORDER BY pc.ICM_PPR_COM_REF_NO";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return ds;
        }
        public void ChangeStatus(int Sequence, int ApproveSequence, string Status)
        {
            string strSQL;
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);


            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@seq", Sequence);
                dbManager.AddParameters(1, "@confirm_emp_seq", ApproveSequence);
                dbManager.AddParameters(2, "@confirm_date", DateTime.Now);
                dbManager.AddParameters(3, "@record_status", Status);

                if (Status == SystemInfo._ApproveRecordStatus)
                {
                    strSQL = "UPDATE ICM_PRE_PURCHASE_REQUEST"
                        + " SET CONFIRM_EMP_SEQ = @confirm_emp_seq"
                        + ", CONFIRM_DATE = @confirm_date"
                        + ", RECORD_STATUS = @record_status"
                        + " WHERE ICM_PPR_SEQ = @seq";
                }
                else
                {
                    strSQL = "UPDATE ICM_PRE_PURCHASE_REQUEST"
                            + " SET RECORD_STATUS = @record_status"
                            + " WHERE ICM_PPR_SEQ = @seq";
                }
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                //dbManager.CreateParameters(4);

                //dbManager.AddParameters(0, "@seq", info.Seq);
                //dbManager.AddParameters(1, "@created_by", info.InfoEmployeeConfirmed.Seq);
                //dbManager.AddParameters(2, "@created_when", DateTime.Now);
                //dbManager.AddParameters(3, "@record_status", SystemInfo._ApproveRecordStatus);

                //strSQL = "INSERT INTO BGM_PROJECT_OPERATION_REPORT"
                //    + "(DESCR, BGM_PROJ_OPER_EXP_SEQ, ICM_PPR_ITEM_SEQ, USED_AMOUNT, CAL_AMOUNT, RPT_TYPE, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                //    + "(SELECT (select icm_ppr_name from icm_pre_purchase_request where icm_ppr_seq = @seq), BGM_PROJ_OPER_EXP_SEQ, ICM_PPR_ITEM_SEQ, COALESCE((ICM_PPR_ITEM_QTY * ICM_PPR_ITEM_PRICE),0)"
                //        + ", COALESCE(- (ICM_PPR_ITEM_QTY * ICM_PPR_ITEM_PRICE),0), 4, @record_status, @created_by, @created_when"
                //    + " FROM ICM_PRE_PURCHASE_REQUEST_ITEM"
                //    + " WHERE ICM_PPR_SEQ = @seq)";

                //dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                //dalRoute.Insert(infoListRoute, (int)info.Seq, (int)info.InfoEmployeeConfirmed.Seq, dbManager);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
